/*
 * To change this license header, choose License Headers in Department Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Department;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import utils.SQLServerDB;

/**
 *
 * @author Canh
 */
public class DepartmentDAO {

    final String DEPARTMENT_READALL = "SELECT * FROM DEPARTMENTS";
    final String DEPARTMENT_READBYID = "SELECT * FROM DEPARTMENTS WHERE ID = ?";
    final String DEPARTMENT_CREATE = "INSERT INTO DEPARTMENTS VALUES (?,?)";
    final String DEPARTMENT_UPDATE = "UPDATE DEPARTMENTS SET NAME = ?, LocationID = ? WHERE ID = ?";
    final String DEPARTMENT_DELETE = "{call Department_Delete(?)}";

    public boolean create(Department p) {
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(DEPARTMENT_CREATE);
            pr.setString(1, p.getName());
            pr.setInt(2, p.getLocationId());
            ResultSet rs = pr.executeQuery();
            return true;
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return false;
    }

    public ArrayList<Department> readAll() {
        ArrayList<Department> projects = new ArrayList<Department>();
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(DEPARTMENT_READALL);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                Department p = new Department(rs.getInt(1), rs.getString(2), rs.getInt(3));
                projects.add(p);
            }
        } catch (SQLException ex) {

        }
        return projects;
    }

    public Department readByID(int id) {
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(DEPARTMENT_READBYID);
            pr.setInt(1, id);
            ResultSet rs = pr.executeQuery();
            if (rs.next()) {
                Department p = new Department(rs.getInt(0), rs.getString(1), rs.getInt(2));
                return p;
            }
        } catch (SQLException ex) {

        }
        return null;
    }

    public boolean update(Department p) {
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(DEPARTMENT_UPDATE);
            pr.setString(1, p.getName());
            pr.setInt(2, p.getLocationId());
            pr.setInt(3, p.getId());
            if (pr.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return false;
    }

    public boolean delete(int id) {
        try {

            SQLServerDB db = new SQLServerDB();
            CallableStatement pr = db.getConn().prepareCall(DEPARTMENT_DELETE);
            pr.setInt(1, id);
            if (pr.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return false;
    }
}
